Query Optimization in Distributed Database Systems

The query optimizer is widely considered to be the most important component of a database management system. It is responsible for taking a user query and searching through the entire space of equivalent execution plans for a given user query and returning the execution plan with the lowest cost. This plan can then be passed to the executer, which can carry out the query. Plans can vary significantly in cost therefore it is important for the optimizer to avoid very bad plans. In this thesis we consider queries in positive relational algrebra form involving the conjunction of projections, selections and joins. The query optimization problem faced by everyday query optimizers gets more and more complex with the ever increasing complexity of user queries. The NP-hard join ordering problem is a central problem that an optimizer must deal with in order to produce optimal plans. Fairly small queries, involving less than 10 relations, can be handled by existing algorithms such as the classic Dynamic Programming optimization algorithm. However, for complex queries or queries involving multiple execution sites in a distributed setting the optimization problem becomes much more challenging and existing optimization algorithms find it difficult to cope with the complexity. In this thesis we present a cost model that allows inter-operator parallelism opportunities to be identified within query execution plans. This allows the response time of a query to be estimated more accurately. We merge two existing centralized optimization algorithms DPccp and IDP1 to create a practically more efficient algorithm IDP1ccp. We propose the novel Multilevel optimization algorithm framework that combines heuristics with existing centralized optimization algorithms. The distributed multilevel optimization algorithm (DistML) proposed in this paper uses the idea of distributing the optimization phase across multiple optimization sites in order to fully utilize the available system resources.